IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IE_SvcEntityStaffedPersonMap_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[IE_SvcEntityStaffedPersonMap_Update]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.IE_SvcEntityStaffedPersonMap_Update

AS
BEGIN

	SET NOCOUNT ON

	UPDATE SvcEntity_AdmStaffedPerson_Map 
	SET AdmStaffedPersonTypeId = STG.AdmStaffedPersonTypeId
	FROM Stg_IE_SvcEntityStaffedPerson_Ctl STG 
	WHERE SvcEntity_AdmStaffedPerson_Map.SvcEntityId = STG.SvcEntityId
	AND SvcEntity_AdmStaffedPerson_Map.AdmPersonId = STG.AdmPersonId
	AND STG.IsExist = 1

	--get SvcEntityStaffedPersonMapId for updated records
	UPDATE Stg_IE_SvcEntityStaffedPerson_Ctl
	SET SvcEntityStaffedPersonMapId = MAP.Id
	FROM SvcEntity_AdmStaffedPerson_Map MAP
	WHERE MAP.SvcEntityId = Stg_IE_SvcEntityStaffedPerson_Ctl.SvcEntityId
	AND MAP.AdmPersonId = Stg_IE_SvcEntityStaffedPerson_Ctl.AdmPersonId
	AND Stg_IE_SvcEntityStaffedPerson_Ctl.IsExist = 1

	UPDATE AgrStaffedPersonEffortYear 
	SET PercentageEffort = STG.nPercentEffort
	FROM Stg_IE_SvcEntityStaffedPerson_Ctl STG 
	WHERE AgrStaffedPersonEffortYear.SvcEntityStaffedPersonMapId = STG.SvcEntityStaffedPersonMapId
	AND AgrStaffedPersonEffortYear.AgrBudgetViewTypeId = STG.AgrBudgetViewTypeId
	AND AgrStaffedPersonEffortYear.SequenceYear = STG.nYear
	AND STG.IsExist = 1

	--get AgrStaffedPersonEffortYearId for updated records
	UPDATE Stg_IE_SvcEntityStaffedPerson_Ctl 
	SET AgrStaffedPersonEffortYearId = PEY.Id
	FROM AgrStaffedPersonEffortYear PEY
	WHERE PEY.SvcEntityStaffedPersonMapId = Stg_IE_SvcEntityStaffedPerson_Ctl.SvcEntityStaffedPersonMapId
	AND PEY.AgrBudgetViewTypeId = Stg_IE_SvcEntityStaffedPerson_Ctl.AgrBudgetViewTypeId
	AND PEY.SequenceYear = Stg_IE_SvcEntityStaffedPerson_Ctl.nYear
	AND Stg_IE_SvcEntityStaffedPerson_Ctl.IsExist = 1

	--insert added records for Year or BudgetViewTypeId
	INSERT	AgrStaffedPersonEffortYear (SvcEntityStaffedPersonMapId, AgrBudgetViewTypeId, PercentageEffort, SequenceYear)
	SELECT	SvcEntityStaffedPersonMapId, AgrBudgetViewTypeId, nPercentEffort, nYear 
	FROM	Stg_IE_SvcEntityStaffedPerson_Ctl
	WHERE 	Stg_IE_SvcEntityStaffedPerson_Ctl.IsExist = 1
		AND AgrStaffedPersonEffortYearId IS NULL

END
GO